/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Entidad.Muestra;
import Entidad.Solicitud;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import javax.swing.JOptionPane;
/**
 
 * @author alexis
 */
public class SolicitudDAO {

    private static final String SQL_INSERT =
            "INSERT INTO solicitudes ("
            + "clientes_id_cliente, fecha_radicacion, hora_radicacion, numero_total_muestras, lugar_de_origen, registro_envio, responsable_quien_recibe, control_cobros, orden_de_servicio "
            + ") VALUES (?, ?, ?, ?, ? ,?, ?, ?, ?)";
    private static final String SQL_SELECT =
            "SELECT clientes_id_cliente, fecha_radicacion, hora_radicacion, numero_total_muestras, lugar_de_origen, registro_envio, responsable_quien_recibe, control_cobros, orden_de_servicio "
            + "  FROM solicitudes WHERE consecutivo_de_solicitud = ?";
    private static final String SQL_SELECT_All =
            "SELECT * "
            + "FROM solicitudes";
    private static final String SQL_UPDATE =
            "UPDATE solicitudes SET "
            + "clientes_id_cliente = ?, fecha_radicacion = ?, hora_radicacion = ?, numero_total_muestras = ?, lugar_de_origen = ?, registro_envio = ?, responsable_quien_recibe = ?, control_cobros = ?, orden_de_servicio = ? "
            + " WHERE "
            + "consecutivo_de_solicitud = ?";

    /* SQL to delete data */
    private static final String SQL_DELETE =
            "DELETE FROM solicitudes WHERE "
            + "consecutivo_de_solicitud = ?";
    
    private static final String SHOW_TABLE_STATUS =
            "SHOW TABLE STATUS LIKE 'solicitudes'";
    
    private static final String SELECCIONAR_POR_FECHA =
            "SELECT * FROM solicitudes WHERE "
            + "fecha_radicacion <= ?";

    public void create(Solicitud sol, Connection conn) throws SQLException {

        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(SQL_INSERT);
            ps.setInt(1, sol.getIdCliente());
            ps.setDate(2, sol.getFechaRadicacion());
            ps.setTime(3, sol.getHoraRadicacion());
            ps.setInt(4, sol.getTotalMuestras());
            ps.setString(5, sol.getLugarOrigen());
            ps.setString(6, sol.getRegistroEnvio());
            ps.setString(7, sol.getQuienRecibe());
            ps.setString(8, sol.getControlCobros());
            ps.setString(9, sol.getOrdenServicio());         
            ps.executeUpdate();
        } finally {
            cerrar(ps);
            //cerrarCnn(conn);
        }
    }

   public Solicitud load(Solicitud sol, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(SQL_SELECT);
            ps.setInt(1, sol.getConsecutivoSolititud());
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return (Solicitud)results.get(0);
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
            //cerrarCnn(conn);
            
        }
    }

       public List loadAll(Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(SQL_SELECT_All);
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
            //cerrarCnn(conn);
        }
    }
 
    public void update(Solicitud sol, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(SQL_UPDATE);
            ps.setInt(1, sol.getIdCliente());
            ps.setDate(2, sol.getFechaRadicacion());
            ps.setTime(3, sol.getHoraRadicacion());
            ps.setInt(4, sol.getTotalMuestras());
            ps.setString(5, sol.getLugarOrigen());
            ps.setString(6, sol.getRegistroEnvio());
            ps.setString(7, sol.getQuienRecibe());
            ps.setString(8, sol.getControlCobros());
            ps.setString(9, sol.getOrdenServicio());      
            ps.executeUpdate();
        } finally {
            cerrar(ps);
            //cerrarCnn(conn);
        }
    }

    public void delete(Solicitud sol, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(SQL_DELETE);
            ps.setInt(1, sol.getConsecutivoSolititud());
            ps.executeUpdate();
        } finally {
            cerrar(ps);
            //cerrarCnn(conn);
        }
    }

    private List getResults(ResultSet rs) throws SQLException {
        List results = new ArrayList();
        while (rs.next()) {
            Solicitud sol = new Solicitud();
            sol.setConsecutivoSolititud(rs.getInt("consecutivo_de_solicitud"));
            sol.setIdCliente(rs.getInt("clientes_id_cliente"));
            sol.setFechaRadicacion(rs.getDate("fecha_radicacion"));
            sol.setHoraRadicacion(rs.getTime("hora_radicacion"));
            sol.setTotalMuestras(rs.getInt("numero_total_muestras"));
            sol.setLugarOrigen(rs.getString("lugar_de_origen"));
            sol.setRegistroEnvio(rs.getString("registro_envio"));
            sol.setQuienRecibe(rs.getString("responsable_quien_recibe"));
            sol.setControlCobros(rs.getString("control_cobros"));
            sol.setOrdenServicio(rs.getString("orden_de_servicio"));      
            
            results.add(sol);
        }
        return results;
    }
    
    public int siguienteNumeroSolicitud(Connection conexion) throws SQLException{
        int resultado;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conexion.prepareStatement(SHOW_TABLE_STATUS);
            rs = ps.executeQuery();
            if (rs.first()) {
                resultado = rs.getInt("Auto_increment");
            } else {
                resultado = 0;
            }
            return resultado;
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
    }
     public List BusquedaUnCriterio(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        try {
            ps = conn.prepareStatement("SELECT * FROM solicitudes WHERE "+criterio1+" = ?");
            setSuitableMethod(1, ps, valores.get(0));
            rs = ps.executeQuery();               
            List results = getResults(rs);         
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
       public List BusquedaDosCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        try {
            ps = conn.prepareStatement("SELECT * FROM solicitudes WHERE "+criterio1+"= ? AND "+criterio2+"= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
      
   public List BusquedaTresCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        try {
           ps = conn.prepareStatement("SELECT * FROM solicitudes WHERE "+criterio1+"= ? AND "+criterio2+"= ? AND "+criterio3+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);    
        }
   }
   public List BusquedaCuatroCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        criterio4 = criterios.get(3);
        try {
           ps = conn.prepareStatement("SELECT * FROM solicitudes WHERE "+criterio1+ "= ? AND "+criterio2+ "= ? AND "+criterio3+ "= ? AND "+criterio4+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            setSuitableMethod(4, ps, valores.get(3));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
   public List BusquedaCincoCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        criterio4 = criterios.get(3);
        criterio5 = criterios.get(4);
        try {
           ps = conn.prepareStatement("SELECT * FROM solicitudes WHERE "+criterio1+ "= ? AND "+criterio2+ "= ? AND "+criterio3+ "= ? AND "+criterio4+ "= ? AND "+criterio5+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            setSuitableMethod(4, ps, valores.get(3));
            setSuitableMethod(5, ps, valores.get(4));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
   public List BusquedaSeisCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        criterio4 = criterios.get(3);
        criterio5 = criterios.get(4);
        criterio6 = criterios.get(5);
        try {
           ps = conn.prepareStatement("SELECT * FROM solicitudes WHERE "+criterio1+ "= ? AND "+criterio2+ "= ? AND "+criterio3+ "= ? AND "+criterio4+ "= ? AND "+criterio5+ "= ? AND "+criterio6+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            setSuitableMethod(4, ps, valores.get(3));
            setSuitableMethod(5, ps, valores.get(4));
            setSuitableMethod(6, ps, valores.get(5));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
    public List BusquedaSieteCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        criterio4 = criterios.get(3);
        criterio5 = criterios.get(4);
        criterio6 = criterios.get(5);
        criterio7 = criterios.get(6);
        try {
           ps = conn.prepareStatement("SELECT * FROM solicitudes WHERE "+criterio1+ "= ? AND "+criterio2+ "= ? AND "+criterio3+ "= ? AND "+criterio4+ "= ? AND "+criterio5+ "= ? AND "+criterio6+ "= ? AND "+criterio7+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            setSuitableMethod(4, ps, valores.get(3));
            setSuitableMethod(5, ps, valores.get(4));
            setSuitableMethod(6, ps, valores.get(5));
            setSuitableMethod(7, ps, valores.get(6));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
     public List BusquedaOchoCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        criterio4 = criterios.get(3);
        criterio5 = criterios.get(4);
        criterio6 = criterios.get(5);
        criterio7 = criterios.get(6);
        criterio8 = criterios.get(7);
        try {
           ps = conn.prepareStatement("SELECT * FROM solicitudes WHERE "+criterio1+ "= ? AND "+criterio2+ "= ? AND "+criterio3+ "= ? AND "+criterio4+ "= ? AND "+criterio5+ "= ? AND "+criterio6+ "= ? AND "+criterio7+ "= ? AND "+criterio8+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            setSuitableMethod(4, ps, valores.get(3));
            setSuitableMethod(5, ps, valores.get(4));
            setSuitableMethod(6, ps, valores.get(5));
            setSuitableMethod(7, ps, valores.get(6));
            setSuitableMethod(8, ps, valores.get(7));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
    public void setSuitableMethod(int indice, PreparedStatement ps, Object obj){
        try {
            switch(obj.getClass().getName()){
                  case "java.lang.String": ps.setString(indice, obj.toString().trim());break;
                  case "java.lang.Integer": ps.setInt(indice, Integer.parseInt(obj.toString()));break;
                  case "java.lang.Boolean": ps.setBoolean(indice, Boolean.parseBoolean(obj.toString().trim()));break;   
                  case "java.sql.Date": ps.setDate(indice, (Date)obj);break; 
                  case "java.sql.Time": ps.setTime(indice, (Time)obj);break; 
            } 
            
        } catch (SQLException | NumberFormatException ex){
        }   
    }
    
    public int[] solicitudesHaceTresMeses(Connection conexion) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.MONTH, -3);
            java.util.Date time = cal.getTime();
            Date haceTresMeses = new Date(time.getTime());
            
            ps = conexion.prepareStatement(SELECCIONAR_POR_FECHA);
            ps.setDate(1, haceTresMeses);
            rs = ps.executeQuery();
            List results = getResults(rs);
            
            if (results.size() > 0) {
                int[] resultados = new int[results.size()];
                for (int i = 0; i < results.size(); i++) {
                    Solicitud solicitud  = (Solicitud) results.get(i);
                    resultados[i] = solicitud.getConsecutivoSolititud();
                }
                return resultados;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
    }
    
    private void cerrar(PreparedStatement ps) throws SQLException {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
            }
        }
    }

    private void cerrarRs(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }
    }

    private void cerrarCnn(Connection cnn) {
        if (cnn != null) {
            try {
                cnn.close();
            } catch (SQLException e) {
            }
        }
    }
    private static  String criterio1;
    private static  String criterio2;
    private static  String criterio3;
    private static  String criterio4;
    private static  String criterio5;
    private static  String criterio6;
    private static  String criterio7;
    private static  String criterio8;
}
